﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;
using Project_1.Models;

namespace Project_1.Controllers
{
    public class HomeController : Controller
    {

        public static SqlConnection conn = new SqlConnection("Data source=(local);Database=AnsBook;user id=sa;password=nh0cpr0");
        public ActionResult Index()
        {

            if (Request.Cookies["userCookie"] != null)
            {
                Session["Username"] = Request.Cookies["userCookie"]["Username"];
            }
            if (conn != null) conn.Close();
            string sqlStm = "SELECT Top 20 [Id],[Title],[Content],[CategoryId],[CreateTime],[UpdateTime],[Views],[Username],[Image]FROM [AnsBook].[dbo].[Questions] ORDER BY CreateTime DESC";

            List<ViewListModel> listRecent = new List<ViewListModel>();
            SqlCommand selectRecent = new SqlCommand(sqlStm, conn);
            try
            {

                conn.Open();
                SqlDataReader reader = selectRecent.ExecuteReader();
                while (reader.Read())
                {

                    ViewListModel temp = new ViewListModel();
                    temp.Image = reader["Image"].ToString();
                    temp.Username = reader["Username"].ToString();
                    temp.Views = int.Parse(reader["Views"].ToString());
                    temp.UpdateTime = DateTime.Parse(reader["UpdateTime"].ToString());
                    temp.CreateTime = DateTime.Parse(reader["CreateTime"].ToString());

                    temp.CategoryID = int.Parse(reader["CategoryId"].ToString());
                    temp.Id = reader["Id"].ToString();
                    temp.Title = reader["Title"].ToString();
                    temp.Content = reader["Content"].ToString();
                    listRecent.Add(temp);
                }
                reader.Close();
            }
            catch { }
            finally
            {
                if (conn != null) conn.Close();
                ViewData["ListQuestion"] = listRecent;
            }


            string sqlStm1 =

            "select top 10 [QuestionID],[Questions.Title], count(Answers.Id) numberofanswer"
            + "FROM ([AnsBook].[dbo].[Questions] INNER JOIN [AnsBook].[dbo].[Answers] ON Questions.Id = Answers.QuestionID)"
            + "group by QuestionID,Questions.Title  order by numberofanswer desc";




            List<ViewListModel> ListTopAnswer = new List<ViewListModel>();
            SqlCommand selectRecent1 = new SqlCommand(sqlStm1, conn);
            try
            {

                conn.Open();
                SqlDataReader reader1 = selectRecent1.ExecuteReader();
                while (reader1.Read())
                {

                    ViewListModel temp = new ViewListModel();
                    temp.Image = reader1["Image"].ToString();
                    temp.Username = reader1["Username"].ToString();
                    temp.Views = int.Parse(reader1["Views"].ToString());
                    temp.UpdateTime = DateTime.Parse(reader1["UpdateTime"].ToString());
                    temp.CreateTime = DateTime.Parse(reader1["CreateTime"].ToString());

                    temp.CategoryID = int.Parse(reader1["CategoryId"].ToString());
                    temp.Id = reader1["Id"].ToString();
                    temp.Title = reader1["Title"].ToString();
                    temp.Content = reader1["Content"].ToString();
                    ListTopAnswer.Add(temp);
                }
                reader1.Close();
            }
            catch { }
            finally
            {
                if (conn != null) conn.Close();
                ViewData["ListTopAnswer"] = ListTopAnswer;
            }
            return View();
        }


        public ActionResult About()
        {
          
            return View();
        }
    }
}
